Select Distinct Column While Joining Tables
Oct 29, 2014
I am attempting to run the following select statement joining multiple tables but in the end result I would like only Distinct/Unique values to be returned in the invlod.lodnum column.
[select pw.schbat, adrmst.adrnam, adrmst.adrln1, adrmst.adrcty, adrmst.adrstc, adrmst.adrpsz,
invlod.lodnum,
shipment.host_ext_id, shipment_line.ordnum, car_move.car_move_id
from aremst join locmst
on (aremst.arecod = locmst.arecod)
and (aremst.wh_id = locmst.wh_id)
[Code] .....
View 4 Replies
ADVERTISEMENT
Sep 6, 2014
I have 2 tables to join.
select ID,FirstName,LastName,Gender from tableA
select ID,BabyFirstName,BabyLastName from tableB
how can I put the babyfirstname into the same row under Firstname from tableA after joining?
i tried this but the babyfirstname appear in new column.
select ID,FirstName,LastName,Gender, babyfirstname
from tableA a
join tableB b on a.id=b.id
View 1 Replies
View Related
Jan 11, 1999
Wuddup,
I am trying to create a view that encapsulates some specific info from many different tables. I have about 30 tables all with exactly the same field names and field types. I want to take 3 of the fields from every table and put them together into one 'VIEW' so I can run more efficient queries. SQL however, doesn't let you 'combine' 2 columns from different tables into one column in the view. (making sense?)
I tried running a 'UNION' but you are specifically NOT allowed to run a union in a create view statement. Anyone have any ideas?
View 1 Replies
View Related
Dec 13, 2013
If you need to inner join 2 tables that have some columns names that are the same, how can you have those columns be named differently in the query result without aliasing them individually?
Tried select a.*,b.* from tbldm a,tblap b where a.id=b.id hoping the col names in the result would have the a.s and b.s in front of them but they didn't.
View 2 Replies
View Related
Aug 22, 2007
My basic situation is this - I ONLY want duplicates, so the oppositeof DISTINCT:I have two tables. Ordinarily, Table1ColumnA corresponds in a one toone ratio with Table2ColumnB through a shared variable. So if I queryTableB using the shared variable, there really should only be onrecord returned. In essence, if I run this and return TWO rows, it isvery bad:select * from TableB where SharedVariable = 1234I know how to join the tables on a single record to see if this is thecase with one record, but I need to find out how many, among possiblymillions of records this affects.Every record in Table1ColumnA (and also the shared variable) will beunique. There is another column in Table1 (I'll call itTable1ColumnC) that will be duplicated if the record in Table2 is aduplicate, so I am trying to use that to filter my results in Table1.I am looking to see how many from Table1 map to DUPLICATE instances inTable2.I need to be able to say, in effect, "how many unique records inTable1ColumnA that have a duplicate in Table1ColumnC also have aduplicate in Table2ColumnB?"Thanks if anyone can help!-- aknoch
View 1 Replies
View Related
Sep 29, 2015
I am trying to join two tables and looks like the data is messed up. I want to split the rows into columns as there is more than one value in the row. But somehow I don't see a pattern in here to split the rows.
This how the data is
Create Table #Sample (Numbers Varchar(MAX))
Insert INTO #Sample Values('1000')
Insert INTO #Sample Values ('1024 AND 1025')
Insert INTO #Sample Values ('109 ,110,111')
Insert INTO #Sample Values ('Old # 1033 replaced with new Invoice # 1544')
Insert INTO #Sample Values ('1355 Cancelled and Invoice 1922 added')
Select * from #Sample
This is what is expected...
Create Table #Result (Numbers Varchar(MAX))
Insert INTO #Result Values('1000')
Insert INTO #Result Values ('1024')
Insert INTO #Result Values ('1025')
Insert INTO #Result Values ('109')
Insert INTO #Result Values ('110')
[Code] ....
How I can implement this ? I believe if there are any numbers I need to split into two columns .
View 2 Replies
View Related
Jun 1, 2001
Attached is my select query, but it is returning the database values twice.
Can anyone tell me how I tell it to stop! A sub query I guess....
SELECT DISTINCT
dbo.Lead_Entry.Lead_ID, dbo.Lead_Entry.Lead_Source, dbo.Lead_Entry.Lead_Approved, dbo.Lead_Entry.Solution, dbo.Lead_Entry.Lead_Date,
dbo.Customer_Company.Company_Name, dbo.Customer_Contacts.Contact_Name
FROM dbo.Customer_Contacts INNER JOIN
dbo.Customer_Company ON dbo.Customer_Contacts.Company_ID = dbo.Customer_Company.Company_ID CROSS JOIN
dbo.Lead_Entry
View 4 Replies
View Related
Oct 19, 2006
hello I am trying to get a distinct on one column and 2 tables but it doesnt work
Table1
ID_Table1
Name1
Number1
Table2
ID_Table2
ID_Table1
Name2
I want to get : ID_Table1, DISTINCT(Name1), Name2
WHERE Name1 LIKE 'A%'
how can I do it ?
thank you
View 4 Replies
View Related
Apr 15, 2015
I have this
SELECT DISTINCT inta, name, PHN#, fROM nydta.adres
WHERE inta <> ' '
I want the distinct for inta because alot of the time phone is blank so those are coming thru i do want all columns, but distinct for inta.
View 1 Replies
View Related
Oct 19, 2006
I know this is a popular problem, but i haven't found an answer to my situation.
I have a table (myTable) with three Columns (Column1, Column2, Column3). Column1 entries are unique (with unique identifier), Column2 entries are not unique.
I want to do a SELECT DISTINCT on myTable so that Column2 is unique and i get all the other columns. I thought something like this would work
SELECT DISTINCT Column2 as Col2, newid() as Col1, Column3 as Col4
-- I am getting uniqueidentifier for each row as well
This doesn't seem to work however. I still get all the rows because Col1 is unique.
since i am using aliases, the usual syntax doesn't work either.
any help would be greatly appreciated!
View 2 Replies
View Related
Jun 22, 2008
Hi guys, I'm hoping there's someone out there with more sql knowledge and experience than me. I'll try to explain everything.
I'm trying to create a select statement but i'm not gettting the required results mainly because i think its a very complicated select.
Here is the scenario.
The table has 12 columns
ProductID Colour MD01 MD02 MD03 MD04 MD05 MD06 MD07 MD08 MD09 MD010
The ProductID is naturally the unique key.
There is always a colour value. But there is not always a value in the MD columns. For example one Product may have values in MD01 MD02 MD03 MD04 MD05 whilst another has values in all MD columns.
My problem is thatI am trying to create a results list based upon selecting distinct values from the colour and md columns
In otherwords i can't have more than only one instance of a word appearing in the recordset list
I'm really struggling with this because there are only 6 colours so if i set distinct purely on 6 colours i only get back 6 rows.
When I try to set disctinct across all the MD columns it seems to ignore it and lists results for example in the table
ProductID Colour MD01 MD02 MD03 MD04 MD05 MD06 MD07 MD08 MD09 MD010
1 red car bike
2 blue bike car train
my select lists results as
red
car
bike
blue
bike
car
train
and it is as if it only carries out the distinct command across the row not across all columns for all rows if you see what i mean?
I need to be able to list all data from all rows that have values in the MD columns and colour column but not list the values more than once and not list "empty" (NULL) columns. Does this make sense?
This is the select statement i wrote.
Select DISTINCT md00, md01, md02, md03, md04, md05, md06, md07, md08, md09, md10, colour FROM TEMPLATES WHERE md00 IS NOT NULL or md01 IS NOT NULL or md02 IS NOT NULL or md03 IS NOT NULL or md04 IS NOT NULL or md05 IS NOT NULL or md06 IS NOT NULL or md07 IS NOT NULL or md08 IS NOT NULL or md09 IS NOT NULL or md10 IS NOT NULL
But it returns empty columns and it returns every instance of the same word so in other words the distinct command doesn't seem to be working at all?
I don't know if this is because of my asp code I am trying to list results with the rescordset?
<%
While ((Repeat1__numRows <> 0) AND (NOT template_rs.EOF))
%>
<%=(template_rs.Fields.Item("md01").Value)%>
<%=(template_rs.Fields.Item("md02").Value)%>
<%=(template_rs.Fields.Item("md03").Value)%>
<%=(template_rs.Fields.Item("md04").Value)%>
<%=(template_rs.Fields.Item("md05").Value)%>
<%=(template_rs.Fields.Item("md06").Value)%>
<%=(template_rs.Fields.Item("md07").Value)%>
<%=(template_rs.Fields.Item("md08").Value)%>
<%=(template_rs.Fields.Item("md09").Value)%>
<%=(template_rs.Fields.Item("md10").Value)%>
<%=(template_rs.Fields.Item("colour").Value)%>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
template_rs.MoveNext()
Wend
%>
I have one more problem. How can I also in addition to being able to list all distinct results list only results for a specific letter
for example
Select DISTINCT md00, md01, md02, md03, md04, md05, md06, md07, md08, md09, md10, colour FROM TEMPLATES WHERE md00 IS NOT NULL or md01 IS NOT NULL or md02 IS NOT NULL or md03 IS NOT NULL or md04 IS NOT NULL or md05 IS NOT NULL or md06 IS NOT NULL or md07 IS NOT NULL or md08 IS NOT NULL or md09 IS NOT NULL or md10 IS NOT NULL WHERE FIRST LETTER ='A'?
I am so far out of my depth here guys I am hoping that someone who has real knowledge of SQL can help me with this statement. I've been pulling my hair out for days now and getting just more and more frustrated listing the same results :(
-BB
View 20 Replies
View Related
Apr 5, 2007
Been trying to come up with a query to filter-down my sample set intodistinct records. For instance, lets say column1 is a sample set,column2 is the parameter, and column3 is a name and column4 is a type(lets also say there is a fifth column (id) that is an id). What Ineed is one record per type per sample only if type is given, if not,then return that record as well.I've used a subquery to get as close to the desired query is aspossible:select * from table1where id in (select min(id) from table1where column1="A"group by column1, column2)Here's an example of all data for sample "A":1 2 3 4----------A 1 X PA 1 Y PA 1 Z PA 2 WA 3 WA 4 T PA 5 U PA 6 V PA 7 TA 7 UA 7 VI want output :1 2 3-------A 1 X PA 2 WA 3 WA 4 T PA 5 U PA 6 V PA 7 TA 7 UA 7 VExcept the above query will exclude the last two records becausecolumn3 is not 'grouped by'.Basically I need to reduce any 'range' of records per sample (columna) where column4 is not null (ie = 'P'), to only one record andkeeping all others. Thanks in advance:-B
View 6 Replies
View Related
Feb 14, 2008
This query demonstrates a problem I have run across:
USE AdventureWorks
GO
-- This query works fine.
SELECT DISTINCT FirstName AS Name1 FROM Person.Contact ORDER BY FirstName
GO
-- This query also works fine.
SELECT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName
GO
-- This query returns error 145
SELECT DISTINCT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName
GO
The last query returns the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified".
It will work if I change ORDER BY to use "Name1" instead of "FirstName", but in the situation I have at hand, the query is generated by third-party software and I don't have the ability to change it.
Can anyone explain why what's going on here? Oddly, this same query will work if I run it against SQL Server 2000.
View 8 Replies
View Related
Dec 16, 2004
I have a table 'wRelated' with the following columns
[related_id] [int]
[channel_id] [int]
[mui] [varchar]
[price_group_id]
[type_id] [int]
[related_mui] [varchar] (100)
[date_started] [smalldatetime]
[date_ended] [smalldatetime]
[date_entered] [datetime]
[deleted] [tinyint],
[rank] [int]
data in column [mui] is repeated as the table has more than one entries for the same [mui],
The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui]
The recordset expected should be something like this.
[mui],[related_mui],[price_group_id],[date_entered],[date_ended] m123,rm345,'pr','12-10-2003',12-12-2004'
null,rm789,'ar','12-1-2003',26-2-2004'
null,rm999,'xy','14-12-2002',12-2-2004'
m777,rm889,'pr','12-12-2004',12-12-2004'
null,rm785,'yy','1-10-2002',12-12-2004'
m888,rm345,'pr','2-8-2003',12-12-2004'
null,rm345,'tt','30-7-2002',12-12-2004'
I have tried Unions and temporary table inserts.
View 1 Replies
View Related
Apr 18, 2014
--------------------------------------------------
SalesOrder-ItemName-Price-Category
-------------------------------------------------
01-Camera-100-Electronic
01-Memory-4GB-10-Memory
01-Battery-5-Battery
02-Keyboad-10-Accessories
02-Mouse-5-Accessories
03-CPU-300-Hardware
03-Motherboad-400-Hardware
From above rows i would like to select rows based on one distinct column SalesOrder, i want output like below.
-----------------------------------
SalesOrder-ItemName-Price-Category
-----------------------------------
01-Camera-100-Electronic
02-Keyboad-10-Accessories
03-CPU-300-Hardware
CREATE TABLE Table1 (SalesOrder varchar(10), ItemName VARCHAR(100), Price INT, Category VARCHAR(100))
[Code] ......
View 2 Replies
View Related
Oct 31, 2014
‘Trying to SELECT INTO a new table all columns of a table based on a DISTINCT value of one column so for example:
SELECT *
INTO new_table
FROM old_name
WHERE old_table.column IS DISTINCT’
View 4 Replies
View Related
Jul 20, 2005
12.) Now you have two different tables - each with two columns.Table #1Single Column2 rows with a value equal to 1 and 2Table #2Single column2 rows with a value equal to 2 and 4Construct a statement returning in a single column all the valuescontained, but not the common values.This is another question that I got in an interview, I missedit.......Thanks,Tim
View 1 Replies
View Related
Jul 6, 2007
Hi, I have the following script segment which is failing:
CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))
INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
Any ideas?
View 2 Replies
View Related
Mar 29, 2007
suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"
I want's some thing like
field1 rowcount
aaa 5
bbb 6
View 1 Replies
View Related
Aug 18, 2007
I have created 3 views, which I then want to join to produce an overall result. The first view returns customer details, along with payment information. The next two views return values only when the customer has purchased extras outside our standard product i.e. if there is no purchase of an extra, then nothing is written to the extra's table. When I join the views together they only return values where data has been matched in all 3 views i.e. extra's have been purchased. Any data that did not match in all 3 view (i.e. no extra's purchased) is either ignored or dropped from the results. So I need my script to return all values even if no data exists in the two extra views.
My scripts are as follows:
Main View
SELECT
CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID,
CUSTOMER_POLICY_DETAILS.HISTORY_ID,
CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER,
CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE,
ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID,
CUSTOMER_INSURED_PARTY.SURNAME,
SYSTEM_INSURER.INSURER_DEBUG,
SYSTEM_SCHEME_NAME.SCHEMENAME,
CUSTOMER_POLICY_DETAILS.POLICYNUMBER,
--TotalPayable
IsNull(SUM(CASE LIST_TRAN_BREAKDOWN_TYPE.IncludeInTotal
WHEN 1 THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS TotalPayable,
--NetPremium
IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID
WHEN 'NET' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS NetPremium,
--IPT
IsNull(SUM(CASE
WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Premium_Section_ID, 1, 3) = 'TAX' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS IPT,
--Fee
IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID
WHEN 'FEE' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS Fee,
--TotalCommission
IsNull(SUM(CASE
WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID, 4, 4) = 'COMM' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT
ELSE 0
END), 0) AS TotalCommission
FROM
ACCOUNTS_CLIENT_TRAN_LINK
INNER JOIN ACCOUNTS_TRANSACTION
ON ACCOUNTS_CLIENT_TRAN_LINK.TRANSACTION_ID = ACCOUNTS_TRANSACTION.TRANSACTION_ID
INNER JOIN ACCOUNTS_TRAN_BREAKDOWN
ON ACCOUNTS_TRANSACTION.TRANSACTION_ID = ACCOUNTS_TRAN_BREAKDOWN.TRANSACTION_ID
INNER JOIN LIST_TRAN_BREAKDOWN_TYPE
ON ACCOUNTS_TRAN_BREAKDOWN.TRAN_BREAKDOWN_TYPE_ID = LIST_TRAN_BREAKDOWN_TYPE.TRAN_BREAKDOWN_TYPE_ID
INNER JOIN CUSTOMER_POLICY_DETAILS
ON CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_ID AND
CUSTOMER_POLICY_DETAILS.HISTORY_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_HISTORY_ID
INNER JOIN SYSTEM_INSURER
ON CUSTOMER_POLICY_DETAILS.INSURER_ID = SYSTEM_INSURER.INSURER_ID
INNER JOIN SYSTEM_SCHEME_NAME
ON CUSTOMER_POLICY_DETAILS.SCHEMETABLE_ID = SYSTEM_SCHEME_NAME.SCHEMETABLE_ID
INNER JOIN CUSTOMER_INSURED_PARTY
ON ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_HISTORY_ID = CUSTOMER_INSURED_PARTY.HISTORY_ID AND
ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_ID = CUSTOMER_INSURED_PARTY.INSURED_PARTY_ID
WHERE
CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE = '2007-08-17' AND
ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID <> 'PAY'
GROUP BY
CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID,
CUSTOMER_POLICY_DETAILS.HISTORY_ID,
CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER,
CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE,
ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID,
CUSTOMER_INSURED_PARTY.SURNAME,
SYSTEM_INSURER.INSURER_DEBUG,
SYSTEM_SCHEME_NAME.SCHEMENAME,
ACCOUNTS_TRANSACTION.Transaction_ID,
CUSTOMER_POLICY_DETAILS.POLICYNUMBER
Add on View 1
CREATE VIEW TOPCARDPA AS
select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'TRPCAE01'
Add on View 2
CREATE VIEW TOPCARDRESC AS
select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'HICRESC01'
Join Result Script
SELECT
TOPCARD.AUTHORISATIONUSER,
TOPCARD.AUTHORISATIONDATE,
TOPCARD.TRANSACTION_CODE_ID,
TOPCARD.SURNAME,
TOPCARD.INSURER_DEBUG,
TOPCARD.SCHEMENAME,
TOPCARD.POLICYNUMBER,
TOPCARD.TotalPayable,
TOPCARD.NetPremium,
TOPCARD.IPT,
TOPCARD.Fee,
TOPCARD.TotalCommission,
TOPCARDPA.SELECTED,
TOPCARDRESC.SELECTED
FROM
dbo.TOPCARD TOPCARD
INNER JOIN dbo.TOPCARDPA TOPCARDPA
ON TOPCARD.POLICY_DETAILS_ID = TOPCARDPA.POLICY_DETAILS_ID AND
TOPCARD.HISTORY_ID = TOPCARDPA.HISTORY_ID
INNER JOIN dbo.TOPCARDRESC TOPCARDRESC
ON TOPCARD.POLICY_DETAILS_ID = TOPCARDRESC.POLICY_DETAILS_ID
AND
TOPCARD.HISTORY_ID = TOPCARDRESC.HISTORY_ID
I have included all the scripts I have used, as others may find them useful, in addition to anyone that is able to provide me with some assistance. Thanks in advance for for the help.
View 2 Replies
View Related
Apr 29, 2007
Hi. I'm new to SQL, and need to join 2 tables... any hints???
table1:id (int)title(varchar(50))body(text)
table2:id (int)title(varchar(50))body(text)
somehow need to get the id, which table the record is from, and the title and body... so if the tables had the information:
table1:id title body1 "first title" "first body"2 "second title" "second body"3 "third title" "third body"
table2:id title body1 "first title" "first body"2 "second title" "second body"3 "third title" "third body"
I would like to get...
id table title body3 1 "third title" "third body"3 2 "third title" "third body"2 1 "second title" "second body"2 2 "second title" "second body"1 1 "first title" "first body"1 2 "first title" "first body"
Does anyone know how to get this? I am fairly flexible if i need to change things...
cheers, eh!
View 1 Replies
View Related
Aug 30, 2007
Hello everyone,I'm starting a new project right now and am trying to cut down on the number of stored procedures and tables I'm gonna have to use and I have run into a dead end.Up till now I have been doing the following: Say I had a PRODUCTS table with a DesignId column and ColorId column. I would then create a DESIGN table (Name, Id) and a COLOR table (Name, Id) to INNER JOIN with the two columns in my PRODUCTS table. And the same goes for all my other tables: ORDERS, CUSTOMERS, LINKS etc...... And in the end I would have a lot of tables and stored procedures for these category columns. So I thought, it would be nice to just have a Categories and Subcategories table for all my category columns for the whole website. That way every time I need to define a category column for any table I can simply just add the values to my Categories and Subcategories table instead of having to create a new table for every category column. Everything is fine and dandy except for trying to INNER JOIN these two tables with more than one column. To get values for one column is no problem:<code> SELECT *, _SubCategories.SubCategoryNameFROM _ProductsINNER JOIN _SubCategoriesON _Products.DesignId = _SubCategories.SubCategoryIdWHERE DesignId = COALESCE (@DesignId, DesignId)</code> But how do you INNER JOIN the ColorId column as well. Both DesignId and ColorId values are in my _SubCategories table. In a stored procedure: Is there any way to create a table and columns. Run a loop statement, with one INNER JOIN . Rerun another loop statement with a new INNER JOIN statement? Would that work or does any one else have an idea what would?Thank you guys for the help. It is much appreciated. Alec
View 11 Replies
View Related
Jan 14, 2008
Hello all,
I have two datatables "customersReached " and "customersGuessed " and I want to combine them into one table only, the problem is that one table exeeded to the other by two fields, so what can I do???????
Mahmoudona
View 4 Replies
View Related
Apr 14, 2004
I've been trying to think about how I can do this. I have forums that I have written built around SQL Server. Basicly you have:
-A users Table
-A Posts Table
-A Replies Table.
Posts and replies have very similar structures. I'd like to be able to merge them and pick out the earliest post for said forum.
1 - is there a way to merge them so that the post date for both the replies and posts tables is contained in 1 column. If not is there a better alternative.
I'd also like to add indexing to the posts so I can do paging. Is there a way for me to add an index number to them while I can sort them anyway i want.
View 1 Replies
View Related
Apr 18, 2006
I am using MS SQL Server 2005 on Windows XP with SQL Server Management Studio Express CTP. I am having issues with my query on joining 2 tables I created using BETWEEN to restrict the Salary. Table 1 is called Employee and Table 2 is called Job_title. The column Job_title_code is the only column that is in both tables which is how I am joining both tables. Here is my SQL query:
Code:
SELECT Employee.*, Job_title.*
From Employee
INNER JOIN Job_title
ON Employee.Job_title_code=Job_title.Job_title_code
WHERE Salary
BETWEEN 50000 AND 500000;
The results I am getting back are:
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Job_title_code'.
I can't figure out how to fix this error. I feel like I have tried everything, so any help will be much appreciated. Thank you.
View 2 Replies
View Related
Jun 22, 2004
Hi,
I have a table with fields as partnerid, contractno.
The partnerid field has the Id number which can be a supplier or a customer.
I need to get the partner id(supplier) and the partner id (customers) of that particular supplier only. I tried with self join but the data is data is replicating.
Data in table
PId ContractNo
20045 1567
435 1567
123 1567
345 1678
1004 1678
I need to display the data in the following format.
PId(Supplier) PId(Customer)
20045 1567
20045 435
20045 123
345 1678
345 1004
But I'm getting the data replicated with all records joined every record.
Give the suggestion.
View 2 Replies
View Related
Oct 10, 2004
G'day,
I have got following 4 tables
Table 1
name age city
jack 20 Melbourne
Nick 30 Bendigo
Russ 28 Sydney
Table 2
name age city Company
jack 20 Melbourne AAA
Nick 30 Bendigo BBB
Russ 28 Sydney AAA
Marty 31 Perth AAA
Table 3
name age city Position
jack 20 Melbourne Manager
Nick 30 Bendigo Manager
Russ 28 Sydney Clerk
Marty 31 Perth Manager
Table 4
name age city datejoined
jack 20 Melbourne 09-09-2001
Nick 30 Bendigo 08-05-2001
Russ 28 Sydney 10-12-2000
Marty 31 Perth 11-11-1999
I want a query which extract the name, age and city from Table 2 (where name,age and city equals table1 values) and position from table3 where position is 'manager' else return null and date joined from table 4 only for the managers else return null.
so the result should be
name age city position datejoined
jack 20 Melbourne Manager 09-09-2001
Nick 30 Bendigo Manager 08-05-2001
Russ 28 Sydney null null
my query
SELECT b. name, b.age, b.city,b.company,c.position,d.datejoined
FROM Table1 a, Table2 b, Table3 c, Table4 d
WHERE
a.age=b.age
and a.name=b.name
and a.city=b.city
and b.age*=c.age
and b.name*=c.name
and b.city*=c.city
and b.position='Manager'
and b.age*=d.age
and b.name*=d.name
and b.city*=d.city
THE RESULT IS
jack 20 Melbourne Manager 09-09-2001
Nick 30 Bendigo Manager 08-05-2001
Russ 28 Sydney null 10-12-2000
When I try to join table4 with table i am getting a exception
Ps: as the original code was in SQL SERVER 6.5 I have to use *= for joins not keywords LEFT JOIN or RIGHT JOIN
hope yo guys can help me
regards
Melb
View 13 Replies
View Related
Feb 12, 2004
I have a bit of an issue that I can not seem to figure out and was hoping to get some feedback/advice from you all.
First a little background. I have two databases and I am adding a new table too one of them. However I need to join the two databases but by columns and the columns I want to use to join them will use different data types and values.
Example database 1 column 1 will be groups.group.id and database 2 column 1 will be users.group.id. However in database 2 (users) the group_id will contain different data.
Database 1 group.id will contain a single integer and database 2 group.id I want to have it contain multiple integers seperated by a comma.
Example code:
select groups.group.id, groups.group.name
from groups, users
where groups.disabled='1'
and users.user_id = $user_id
and groups.group.id ? users.group.id
The "?" is where I am having trouble. Does anyone know of a way to join two databases by columns using different data types?
Thanks in advance for any input.
T
View 6 Replies
View Related
Apr 18, 2008
Hi,
i have some sql experience and can link tables but the link i am trying to get is not displaying how i need it to
here is the code i am using, which display logical results, but not the ones i need :P
qry = "SELECT * FROM wce_contact INNER JOIN wce_mailer_link ON wce_contact.UNIQUEID = wce_mailer_link.Contactid LEFT JOIN wce_mailer ON wce_mailer.uniqueid = wce_mailer_link.mailerid RIGHT JOIN wce_mailer_attachments ON wce_mailer_attachments.uniqueid = wce_mailer.fileid WHERE wce_contact.uniqueid = '"& Request.QueryString("id") &"'"
Ok i have these tables
wce_contact
This has the contacts name and address
wce_mailer
This holds the details of the mailer and a link to the wce_mailer_attachments, there would be multiple rows in wce_mailer_attachments table which link to 1 row in wce_mailer.
wce_mailer_link
This holds the wce_contact uniqueid, and the wce_mailer uniqueid. there will be many contacts to many mailers
wce_mailer_attachments
This holds an individual row for one attachment, but the uniqueid would be the same for multiple rows, Dependant on how many attachments the users adds. i.e. one mailer could have several attachments, they would all have the same uniqueid.
Basically the results i am getting using the join i built are displaying each attachment as a separate row when i display the mailers assigned to a contacts record. i need them to display in one single row where the uniqueids are the same in the wce_mailer_attachments and they match the only fileid in wce_mailer.
Hope that makes sense.
Thanks for any help
View 1 Replies
View Related
May 18, 2008
Hi guys can you help me joining three tables?
I used to join all those three but there's a redundant on it's value..
Table1
IDAccount
1A
2B
3C
Table 2
ID Name
1A
2B
3C
Table 3
AccountName
456ABC
456ABC
456ABC
with the following result:(what query should I use?)
IDNameAcctName AccountID2Name2ID3Name3
1AABC 456 2B3C
View 1 Replies
View Related
Jun 15, 2008
Hello friends,
Im a begginer in SQL Server and I just to ask for help on how to this this tables
table1
Field1
123
1234
12
154
123
321
123
21
Table2
Field1
test1
test2
test3
test4
test5
test6
desired output
Field1 Field1
123 test1
1234 test2
12 test3
154 test4
123 test5
321 test6
123
21
can i ask for script how to join this table? i try this script but it doest work.
select table1.col1,table2.col1
from table1
left join table2
ON table1.col1 is NOT NULL
Thanks in advance and more power!
View 2 Replies
View Related
Aug 10, 2005
Hi all!
I have a problem joining three tables. My tables are:
rooms: room_id ¦ room_name
computers: computer_id ¦ computer_name ¦ room_id
bookings: booking_id ¦ computer_id ¦ date
I want to join them so that i get a listing that displays all room names, and if there is a booking for any computer in that room I also want to display the computer name and the booking date next to the room name. If one room has bookings for several computers I would like to display that room several times.
I have gotten a correct result with:
SELECT rooms.room_name, t.computer_name, temp.date FROM rooms LEFT JOIN (SELECT computers.room_id, computers.computer_name, bookings.date FROM computers JOIN bookings ON (bookings.computer_id = computers.computer_id)) AS temp ON (rooms.room_id = temp.room_id)
but I would like to use something shorter like:
SELECT rooms.room_name, computers.computer_name, bookings.date from computers INNER JOIN bookings ON (computers.computer_id = bookings.computer_id) RIGHT JOIN rooms ON (computers.room_ID = rooms.room_ID)
That however gives me a list where every combination of rooms and computers is listed and bookings displayed when there is a match. I don't really get why this doesn't work and I've been staring at it for days. Could somebody help me out? (I'm using PHP and MySQL)
Thanks,
Nick
View 2 Replies
View Related
Dec 9, 2005
Hi all..
Let say i have 3 tables (tblA, tblB, tblC). I want to get all data in tblA. At the same time, i want to join the tables. If value in column aStatus equal to 1, it will get the value from tblB. If the column value is 2, it will refer to tblC. Else, if it 0, no need to refer to any table. just like inner join.. but i dun get the logic of how im going to do that.. can someone advise me on how to do the joining? please refer to picture below.
View 8 Replies
View Related
