January 28, 2016 at 5:26 am
Hello All,
I have somthing like this :
Table 1
CustID Name
116 John
Table2
LangID CustID LangName
001 116 English
002 116 French
Table3
AvailID CustID Date
101 116 Jan 08 2016
What I'm trying to accomplish is to return all columns in Table 1 where LangName from Table 2 = 'English'
simple approach:
select * from Table1, Table2, Table3 where Table2.LangName = 'English'
The problem is I end up with:
116 English Jan 08 2016
116 French Jan 08 2016
I just need the first row from table 1
Any help is appreciated.
Thanks again
January 28, 2016 at 5:30 am
There's only one row in Table1. If you want your result set only to have one row, how do you decide whether to take English or French?
John
January 28, 2016 at 5:36 am
Thanks for the reply.
Basically I'm searching for all records in Table1 where the LangName = 'English', I only need to result set from table1.
However because the same record in Table1 might have multiple records in Table2 I get multiple records of the same CustID.
In simple terms, I want to return the row(s) of customers in Table1 where the LangName = 'English'.
thanks
January 28, 2016 at 5:38 am
You use cartesian product: select * from Table1, Table2, Table3 where Table2.LangName = 'English'
Try with joins:
select *
from Table1 t1
inner join Table2 t2 on t1.CustID = t2.CustID
inner join Table3 t3 on t3.CustID t2.CustID
where t2.LangName = 'English'
Igor Micev,My blog: www.igormicev.com
January 28, 2016 at 5:42 am
THanks Igor,
I did use join, the problem is I get multiple records of the same CustID since there are 2 records in Table2 of CustID (116), as soon as I join Table2 it pulls both records.
January 28, 2016 at 5:51 am
tonytohme (1/28/2016)
THanks Igor,I did use join, the problem is I get multiple records of the same CustID since there are 2 records in Table2 of CustID (116), as soon as I join Table2 it pulls both records.
--Table1
create table table1(
CustID int, Name varchar(100))
insert into table1
select 116 ,'John'
--Table2
create table Table2(
LangID int, CustID int, Name varchar(100))
insert into table2
select 001, 116, 'English'
insert into table2
select 002, 116, 'French'
--Table3
create table Table3(
AvailID int, CustID int, [Date] date )
insert into Table3(AvailID,CustID,[Date])
select 101, 116, '2016-01-08'
select *
from Table1 t1
inner join Table2 t2 on t1.CustID = t2.CustID
inner join Table3 t3 on t3.CustID = t2.CustID
where t2.Name = 'English'
CustIDNameLangIDCustIDNameAvailIDCustIDDate
116John1116English1011162016-01-08
Igor Micev,My blog: www.igormicev.com
January 28, 2016 at 5:56 am
THanks Igor I will try it out, I just noticed I am using Left Join, not sure if that was the issue.
Thank you very much
January 28, 2016 at 12:27 pm
Since you say that you only want data from table t1 if it satisfies the condition that it has "English" in table2, I would keep the query close to these semantics:
SELECT t1.CustID, t1.Name
FROM dbo.Table1 AS t1
WHERE EXISTS
(SELECT *
FROM dbo.Table2 AS t2
WHERE t2.CustID = t1.CustID
AND t2.LangName = 'English');
January 28, 2016 at 1:02 pm
Hugo,
Many thanks, that was the only query that worked for me 🙂 Awesome!!!
Thanks a lot
January 28, 2016 at 1:52 pm
tonytohme (1/28/2016)
Hugo,Many thanks, that was the only query that worked for me 🙂 Awesome!!!
Thanks a lot
Good! Now test it with a much larger and more varied selection of test data. The description and data in your original post was way too incomplete to assess whether this query actually works.
January 28, 2016 at 2:27 pm
Hugo Kornelis (1/28/2016)
tonytohme (1/28/2016)
Hugo,Many thanks, that was the only query that worked for me 🙂 Awesome!!!
Thanks a lot
Good! Now test it with a much larger and more varied selection of test data. The description and data in your original post was way too incomplete to assess whether this query actually works.
And the bad approach was the cartesian product. In the above query with joins you can simply combine which columns to select from all tables.
Igor Micev,My blog: www.igormicev.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply