Left join sql Request (double mixing left join) 
Author Message
 Left join sql Request (double mixing left join)

Here is two SQL Request that I have do :

Request #1 :
SELECT CARD.*
FROM CARD LEFT JOIN [ZONE] ON CARD.ZONE = ZONE.NAME
WHERE (((ZONE.NAME) Is Null));

Request #2 :
SELECT CARD.*
FROM CARD LEFT JOIN [GROUP] ON CARD.ZONE = GROUP.NAME
WHERE (((GROUP.NAME) Is Null));

I need to put this two request in only one request because Card.zone
containt information that can be found in Group table (Group.Name) or in
table Zone (Zone.Name). How can I do this to have only one SQL request ?

Thanks,
Alex



Mon, 18 Jul 2005 01:13:47 GMT
 Left join sql Request (double mixing left join)

I think if you specified your tables structures,
their fields and some sample data, it would be easier
to understand your scenario rather than spend time
guessing what the table structures exactly look like
and what you are trying to accomplish exactly in
"english terms", rather than "SQL Syntax terms".


Quote:

> Here is two SQL Request that I have do :

> Request #1 :
> SELECT CARD.*
> FROM CARD LEFT JOIN [ZONE] ON CARD.ZONE = ZONE.NAME
> WHERE (((ZONE.NAME) Is Null));

> Request #2 :
> SELECT CARD.*
> FROM CARD LEFT JOIN [GROUP] ON CARD.ZONE = GROUP.NAME
> WHERE (((GROUP.NAME) Is Null));

> I need to put this two request in only one request because Card.zone
> containt information that can be found in Group table (Group.Name) or in
> table Zone (Zone.Name). How can I do this to have only one SQL request ?

> Thanks,
> Alex



Mon, 18 Jul 2005 01:49:08 GMT
 Left join sql Request (double mixing left join)
Here is more explication :

Structure for table Card
Card.ID = AutoNumber
Card.Comment = A Comment
Card.Zone = Name of zone where the card have can be used. This zone name
need to be found in table Zone (in the field 'Name') or in table Group (in
the name field again)

Structure for table zone :
Zone.id = Autonumber
Zone.name = A value that can be found in Card.zone (not mandatory)

Structure for table Group :
Group.id = Autonumber
Group.name = A value that can be found in Card.zone (not mandatory)

Example #1:

If I have the foloowing data :
Card. Id = 4
Card.comment = Hello
Card.Zone = abc

Zone.name = abc

This is ok.

Example #2:
It can be also the following  data :
Card. Id = 4
Card.comment = Hello
Card.Zone = abc

Group.name = abc

This is also ok.

Example #3:
It can be also the following  data :
Card. Id = 4
Card.comment = Hello
Card.Zone = def

Group.name = abc
Zone.Name = jhi

This is not correct because I need to find 'def' in either groupe.name or
zone.name. This is not the case, so in the result of my request I want to
have the record from the table Card that match nothing (CardId=4,
Card.Comment=Hello, Card.zone=def).

Now, I want make only one sql queries to verify if card.zone (value 'abc')
is NOT present in at least one of the two table (zone or group).


Quote:
> I think if you specified your tables structures,
> their fields and some sample data, it would be easier
> to understand your scenario rather than spend time
> guessing what the table structures exactly look like
> and what you are trying to accomplish exactly in
> "english terms", rather than "SQL Syntax terms".



> > Here is two SQL Request that I have do :

> > Request #1 :
> > SELECT CARD.*
> > FROM CARD LEFT JOIN [ZONE] ON CARD.ZONE = ZONE.NAME
> > WHERE (((ZONE.NAME) Is Null));

> > Request #2 :
> > SELECT CARD.*
> > FROM CARD LEFT JOIN [GROUP] ON CARD.ZONE = GROUP.NAME
> > WHERE (((GROUP.NAME) Is Null));

> > I need to put this two request in only one request because Card.zone
> > containt information that can be found in Group table (Group.Name) or in
> > table Zone (Zone.Name). How can I do this to have only one SQL request ?

> > Thanks,
> > Alex



Mon, 18 Jul 2005 02:24:23 GMT
 Left join sql Request (double mixing left join)
Let me know if this isn't what you want.

What i understood is, you want to run a single
query that will tell you which CARD does not
exist in both the ZONE and GROUP table.
If that is correct, then the SP called procInvalidCards
should do what you want.

I spent two hours on this, and i found myself asking
a question about using LEFT OUTER JOINs in SQL
Server, I am going to find the time after to
better understand LEFT OUTER JOINs, something
got me confused...

Anyhow, open SQL Server Query Analyzer and paste
the following code in there, and then run it.
First, make sure you choose the DB in which you want to
execute this code, in my case, I chose to run this in NorthwindCS.

ALTER PROCEDURE test1

AS

EXEC ('CREATE PROCEDURE "procInvalidCards"

AS

SELECT Card.ID, Card.Comment, Card.Zone

FROM Card LEFT JOIN

(SELECT NAME FROM Zone

UNION SELECT NAME FROM Groups) AS X

ON Card.Zone = X.NAME

WHERE X.NAME Is Null')

--EXEC ('DROP TABLE Card')

--EXEC ('DROP TABLE Zone')

--EXEC ('DROP TABLE Groups')

EXEC ('CREATE TABLE Card

(

ID int Primary Key,

Comment varchar(50),

Zone varchar(50)

)')

INSERT INTO Card VALUES(4, 'Hello', 'def')

INSERT INTO Card VALUES(5, 'Test', 'abc')

INSERT INTO Card VALUES(6, 'Testing', 'jhi')

EXEC ('CREATE TABLE Zone

(

ID int Primary Key,

Name varchar(50)

)')

INSERT INTO Zone VALUES(1, 'abc')

EXEC ('CREATE TABLE Groups

(

ID int Primary Key,

Name varchar(50)

)')

INSERT INTO Groups VALUES(1, 'jhi')

Then, go and open NorthwindCS.adp file for example,

and double-click procTest1 in order to execute its contents.

Once you do that, a new SP will be created called

procInvalidCards, execute that and see if this is what you

wanted as a result?


Quote:
> Here is more explication :

> Structure for table Card
> Card.ID = AutoNumber
> Card.Comment = A Comment
> Card.Zone = Name of zone where the card have can be used. This zone name
> need to be found in table Zone (in the field 'Name') or in table Group (in
> the name field again)

> Structure for table zone :
> Zone.id = Autonumber
> Zone.name = A value that can be found in Card.zone (not mandatory)

> Structure for table Group :
> Group.id = Autonumber
> Group.name = A value that can be found in Card.zone (not mandatory)

> Example #1:

> If I have the foloowing data :
> Card. Id = 4
> Card.comment = Hello
> Card.Zone = abc

> Zone.name = abc

> This is ok.

> Example #2:
> It can be also the following  data :
> Card. Id = 4
> Card.comment = Hello
> Card.Zone = abc

> Group.name = abc

> This is also ok.

> Example #3:
> It can be also the following  data :
> Card. Id = 4
> Card.comment = Hello
> Card.Zone = def

> Group.name = abc
> Zone.Name = jhi

> This is not correct because I need to find 'def' in either groupe.name or
> zone.name. This is not the case, so in the result of my request I want to
> have the record from the table Card that match nothing (CardId=4,
> Card.Comment=Hello, Card.zone=def).

> Now, I want make only one sql queries to verify if card.zone (value 'abc')
> is NOT present in at least one of the two table (zone or group).



> > I think if you specified your tables structures,
> > their fields and some sample data, it would be easier
> > to understand your scenario rather than spend time
> > guessing what the table structures exactly look like
> > and what you are trying to accomplish exactly in
> > "english terms", rather than "SQL Syntax terms".



> > > Here is two SQL Request that I have do :

> > > Request #1 :
> > > SELECT CARD.*
> > > FROM CARD LEFT JOIN [ZONE] ON CARD.ZONE = ZONE.NAME
> > > WHERE (((ZONE.NAME) Is Null));

> > > Request #2 :
> > > SELECT CARD.*
> > > FROM CARD LEFT JOIN [GROUP] ON CARD.ZONE = GROUP.NAME
> > > WHERE (((GROUP.NAME) Is Null));

> > > I need to put this two request in only one request because Card.zone
> > > containt information that can be found in Group table (Group.Name) or
in
> > > table Zone (Zone.Name). How can I do this to have only one SQL request
?

> > > Thanks,
> > > Alex



Mon, 18 Jul 2005 07:00:21 GMT
 Left join sql Request (double mixing left join)



Quote:
> Here is two SQL Request that I have do :

> Request #1 :
> SELECT CARD.*
> FROM CARD LEFT JOIN [ZONE] ON CARD.ZONE = ZONE.NAME
> WHERE (((ZONE.NAME) Is Null));

> Request #2 :
> SELECT CARD.*
> FROM CARD LEFT JOIN [GROUP] ON CARD.ZONE = GROUP.NAME
> WHERE (((GROUP.NAME) Is Null));

> I need to put this two request in only one request because Card.zone
> containt information that can be found in Group table (Group.Name) or in
> table Zone (Zone.Name). How can I do this to have only one SQL request ?

> Thanks,
> Alex



Mon, 25 Jul 2005 03:02:28 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. INNER JOIN SQL Error

2. Question: Outer Joins in MS SQL-Server when using Sub-Reports of Crystal Reports

3. Complex SQL Join Help Needed Please :-)

4. Wrong result from left join and subquery in SQL 6.5

5. SQL Server Nested INNER JOIN Update

6. SQL join to return ALL data from three related tables

7. Join Access SQLServer Table with Access Table

8. Getting RecordCount From Joined Tables

9. INNER JOIN

10. Join Help Needed Please

11. Numeric (link) WHERE after an INNER JOIN

12. Multi Table Joins


 
Powered by phpBB® Forum Software