Hello,
I want to be able to view data from 3 tables using the JOIN statement, but
I'm not sure of how to do it. I think i don't know the syntax of the joins.I
imagine this is easy for the experienced - but Im not.
Allow me to explain:
I have 2 Tables: PERSON and SIGN
PERSON
------
PersonNo int (Primary Key)
Name varchar(50)
StarSign int
FavFood int
SIGN
----
StarSign int (Primary Key)
StarSignName varchar(50)
Relationship: SIGN has a one-to-many relationship with PERSON. The linking
field is called 'StarSign'.
Question 1:
I want to display all the peoples names, and their star sign (whether they
have one or not).
Answer 1:
SELECT PERSON.Name, SIGN.StarSignNa me
FROM PERSON LEFT OUTER JOIN SIGN ON PERSON.StarSign = SIGN.StarSign;
No problems there. But now I want to do the same thing, but have their
favourite food displayed as well. So an additional table is needed:
FOOD
----
FavFood int (Primary Key)
FavFoodName varchar(50)
Relationship: FOOD has a one-to-many relationship with PERSON. The linking
field is called 'FavFood'.
Question 2:
I want to display all the peoples names, their star signs (whether they
have one or not), and their favourite food (whether they have one or not).
Answer 1:
???
I'm not sure what to do. Notice that I want to use an LEFT OUTER JOIN so ALL
the rows from table PERSON will appear 'irrespective' of whether they have
related records in the other tables.
Jack. 3 1588
Since PERSON is on the many-side in both cases, it's easy. basically, this is
the case where you have several lookup values, each of which is optional.
SELECT PERSON.Name, SIGN.StarSignNa me
FROM PERSON LEFT JOIN
SIGN ON PERSON.StarSign = SIGN.StarSign
LEFT JOIN
FOOD ON PERSON.FavFood = FOOD.FavFood;
(presumably, this is hypothetical, and I don't need to mention table/field
naming issues)
On Wed, 9 Nov 2005 23:02:40 +0800, "Jack Smith" <ja*******@nosp am.co.uk>
wrote: Hello,
I want to be able to view data from 3 tables using the JOIN statement, but I'm not sure of how to do it. I think i don't know the syntax of the joins.I imagine this is easy for the experienced - but Im not.
Allow me to explain: I have 2 Tables: PERSON and SIGN
PERSON ------ PersonNo int (Primary Key) Name varchar(50) StarSign int FavFood int
SIGN ---- StarSign int (Primary Key) StarSignName varchar(50)
Relationship : SIGN has a one-to-many relationship with PERSON. The linking field is called 'StarSign'.
Question 1: I want to display all the peoples names, and their star sign (whether they have one or not). Answer 1: SELECT PERSON.Name, SIGN.StarSignNa me FROM PERSON LEFT OUTER JOIN SIGN ON PERSON.StarSign = SIGN.StarSign;
No problems there. But now I want to do the same thing, but have their favourite food displayed as well. So an additional table is needed:
FOOD ---- FavFood int (Primary Key) FavFoodName varchar(50)
Relationship : FOOD has a one-to-many relationship with PERSON. The linking field is called 'FavFood'.
Question 2: I want to display all the peoples names, their star signs (whether they have one or not), and their favourite food (whether they have one or not). Answer 1: ???
I'm not sure what to do. Notice that I want to use an LEFT OUTER JOIN so ALL the rows from table PERSON will appear 'irrespective' of whether they have related records in the other tables.
Jack.
Thank-you! One thing though if possible - can you repost your solution, but
nest the brakets around the joins.
Final thanks...
Jack.
"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:4a******** *************** *********@4ax.c om... Since PERSON is on the many-side in both cases, it's easy. basically, this is the case where you have several lookup values, each of which is optional.
SELECT PERSON.Name, SIGN.StarSignNa me FROM PERSON LEFT JOIN SIGN ON PERSON.StarSign = SIGN.StarSign LEFT JOIN FOOD ON PERSON.FavFood = FOOD.FavFood;
(presumably, this is hypothetical, and I don't need to mention table/field naming issues)
On Wed, 9 Nov 2005 23:02:40 +0800, "Jack Smith" <ja*******@nosp am.co.uk> wrote:
Hello,
I want to be able to view data from 3 tables using the JOIN statement, but I'm not sure of how to do it. I think i don't know the syntax of the joins.I imagine this is easy for the experienced - but Im not.
Allow me to explain: I have 2 Tables: PERSON and SIGN
PERSON ------ PersonNo int (Primary Key) Name varchar(50) StarSign int FavFood int
SIGN ---- StarSign int (Primary Key) StarSignNam e varchar(50)
Relationshi p: SIGN has a one-to-many relationship with PERSON. The linking field is called 'StarSign'.
Question 1: I want to display all the peoples names, and their star sign (whether they have one or not). Answer 1: SELECT PERSON.Name, SIGN.StarSignNa me FROM PERSON LEFT OUTER JOIN SIGN ON PERSON.StarSign = SIGN.StarSign;
No problems there. But now I want to do the same thing, but have their favourite food displayed as well. So an additional table is needed:
FOOD ---- FavFood int (Primary Key) FavFoodName varchar(50)
Relationshi p: FOOD has a one-to-many relationship with PERSON. The linking field is called 'FavFood'.
Question 2: I want to display all the peoples names, their star signs (whether they have one or not), and their favourite food (whether they have one or not). Answer 1: ???
I'm not sure what to do. Notice that I want to use an LEFT OUTER JOIN so ALL the rows from table PERSON will appear 'irrespective' of whether they have related records in the other tables.
Jack.
Jack Smith (ja*******@nosp am.co.uk) writes: Thank-you! One thing though if possible - can you repost your solution, but nest the brakets around the joins.
I'd rather not...
Personally, I would write Steve's solution as:
SELECT P.Name, S.StarSignName, F.FoodName
FROM PERSON P
LEFT JOIN SIGN S ON P.StarSign = S.StarSign
LEFT JOIN FOOD F ON P.FavFood = F.Food;
You can add parentheses to your heart's content, but for this query
it would add more confusion than necessary.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Jim Garrison |
last post by:
Scenario:
1) Create a GLOBAL TEMPORARY table and populate it with
one (1) row.
2) Join that table to another with about 1 million rows.
The join condition selects a few hundred rows.
Performance: 4 seconds, the system is doing a full-table
scan of the second table, and the Explain Plan output
|
by: eXavier |
last post by:
Hello,
I have query joining several tables, the last table is joined with LEFT
JOIN. The last table
has more then million rows and execution plan shows table scan on it. I have
indexed columns
on which the join is made. If I replace LEFT JOIN with INNER JOIN, index is
used and execution
takes few seconds but with LEFT JOIN there is a table scan , so the
execution
takes several minutes. Does using outer joins turn off indexes? Missed I
|
by: Phillip |
last post by:
Select member
from NameList
Inner join Members
on (Left(Namelist.NameID,5) = Members.ID
OR (left(namelist.SSN,9) = Members.ssn
OR (Left(namelist.CustID,9) + '*01' = Members.CustID)
where
namelist.name <> ''
How do I speed up a process like this? Can I create indexes on the
|
by: rjaw |
last post by:
Hi there,
we have a problem using the left outer join-operation on a db2
database, v7, running on z/os.
Defined are two tables, table1 and table2. Both tables have a primary
key of field1, field2 and field3. There two tables exist in two
different schemas. Also, there are two view, view1 and view2. View1
joins the table1 on both of the schemas, also view2 for the table2 on
both schemas.
|
by: gimme_this_gimme_that |
last post by:
I have an employee table with two columns, one named login, the other
named otherdata.
I have a list of login values, some of which do not exist in the
employee table.
I want to fetch the data in the employee.otherdata column and return
nulls when there is no match.
....
| |
by: GarryJones |
last post by:
I think the following statement ....
$ml_collect='SELECT *, DATE(CONCAT(field1, field2)) AS thedate FROM
ml_lopp LEFT JOIN scfmforening ON (scfmforening.scfmnum =
ml_lopp.scfmnum) LEFT JOIN ml_tidplats ON (ml_tidplats.loppnum =
ml_lopp.loppnum) ORDER BY thedate';
.....would work if "field1" and "field2" were in the table "ml_lopp"
However, "field1" and "field2" are in the table "ml_tidplats" and
|
by: Daniel Peter |
last post by:
How can i use Left Join in an SQL as well as use Like command in the same sql statement. And can you please give me various examples of the codes i can use as well as the table generated from the SQL statement.
|
by: ltansey |
last post by:
I have a problem concerning a join statement query, the two table are called PublicHouse & PublicHouseBeer, below are the following tables are there attributes;
PublicHouse Table
create table "PublicHouse"
(PublicHouseID char (20),
PubName Char (20),
Address varchar (20),
Town char (20),
County varchar (20));
|
by: ltansey |
last post by:
Simple Join Statement Help
--------------------------------------------------------------------------------
I have a problem concerning a join statement query, the two table are called PublicHouse & PublicHouseBeer, below are the following tables are there attributes;
PublicHouse Table
create table "PublicHouse"
(PublicHouseID char (20),
PubName Char (20),
|
by: fastPace |
last post by:
I created an SQL statement, left joining three tables. When I use the statement as the recordsource for my form in datasheetview, I cannot input data into any of the fields. Can anyone help me with this?
Here is my SQL statement:
strSQL = " SELECT tblAccounts.sNumber, tblAccounts.sName, tblAccounts.sType, tblUnAdj.sngUnAdjDR, tblUnAdj.sngUnAdjCR, tblAJESummary.sngAJEDR, tblAJESummary.sngAJECR " & _
" FROM (tblAccounts LEFT JOIN tblUnAdj...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |