I am trying to figure out some sql syntax, and I could use some help. This
is my first atempt at joins, so bear with me.
I have a table (A) which looks like the following
ID Data Source
-------------------------
1 abcdef 100
2 abcdef 100
3 abcdef 200
4 abcdef 200
5 abcdef 200
A second table (B) which looks like the following
Key ID
------------------------
Key1 1
Key1 2
Key1 3
Key1 4
Key2 1
Key2 2
Essentially, A is a table of items, and B is a table of where those items
have been used (Key1 is like an invoice which has items 1-4 on it, Key2 is a
second invoice with 1 and 2.) Source, in table A, is like the item
supplier.
I would like to get a list of every invoice (Key) that has used a part (ID)
from a particular Source.
So, for example, I would like to query for source 100 and get back (Key1,
Key2) or query for source 200 and get back only Key1.
To this end, I tried
"SELECT DISTINCT B.Key FROM B JOIN A ON (B.ID = A.ID) WHERE (A.Source =
100)"
But I got an empty recordset, so something is amiss.
Any help is greatly appreciated.
Thanks,
-d 2 1899
Please post some code that will actually reproduce the problem. Your query
worked for me and here's the proof:
/* (My assumptions about your tables and keys) */
CREATE TABLE A (id INTEGER PRIMARY KEY, data VARCHAR(10), source INTEGER NOT
NULL)
CREATE TABLE B ([key] VARCHAR(10), id INTEGER NOT NULL REFERENCES A (id),
PRIMARY KEY ([key],id))
INSERT INTO A (id, data, source)
SELECT 1, 'abcdef', 100 UNION ALL
SELECT 2, 'abcdef', 100 UNION ALL
SELECT 3, 'abcdef', 200 UNION ALL
SELECT 4, 'abcdef', 200 UNION ALL
SELECT 5, 'abcdef', 200
INSERT INTO B ([key],id)
SELECT 'Key1', 1 UNION ALL
SELECT 'Key1', 2 UNION ALL
SELECT 'Key1', 3 UNION ALL
SELECT 'Key1', 4 UNION ALL
SELECT 'Key2', 1 UNION ALL
SELECT 'Key2', 2
SELECT DISTINCT B.[key]
FROM B JOIN A
ON B.id = A.id
WHERE A.source = 100
Result:
key
----------
Key1
Key2
(2 row(s) affected)
--
David Portas
SQL Server MVP
--
"David Portas" <RE****************************@acm.org> wrote in message
news:Vd********************@giganews.com... Please post some code that will actually reproduce the problem. Your query worked for me and here's the proof:
Goodness, sorry to waste your time, and thanks for the help nonetheless. It
seems I was querying for nonexistent data.
-d This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Pierre Fortin |
last post by:
This quest for understanding started very innocently...
A simple error on my part, passing on args as "args" instead of "*args" to
os.path.join() led me to wonder why an error wasn't raised...
...
|
by: Martin |
last post by:
I am now working on SQL Server 2000 having had previous experience on
a different database. Both of the OUTER JOIN syntaxes is different
from what I am used to and I am finding it slightly...
|
by: mheydman |
last post by:
I apologize if this has been asked before- I searched google but could
not find a concrete answer.
I recently inherited a database whose t-sql code is written in a format
that I find difficult...
|
by: Matt |
last post by:
Hello
I have to tables ar and arb, ar holds articles and a swedish
description, arb holds descriptions in other languages.
I want to retreive all articles that match a criteria from ar and...
|
by: Good Man |
last post by:
Hi there
Yes, I've read about JOINs, albeit after coding for a couple of years
already using queries like the following:
"SELECT m.LastName, m.FirstName, o.Address FROM members m, offices o...
|
by: Ian Boyd |
last post by:
i know nothing about DB2, but i'm sure this must be possible.
i'm trying to get a client to create a view (which it turns out is called a
"Logical" in DB2). The query needs a LEFT OUTER JOIN, but...
|
by: Doug |
last post by:
Hi,
I'm more familiar with MSSQL than Access syntax and have run into a problem
with correctly putting ( )'s around the joins in a 3 table query. I want to
INNER JOIN lenders and accounts and...
|
by: MP |
last post by:
Hi
trying to begin to learn database using vb6, ado/adox, mdb format, sql
(not using access...just mdb format via ado)
i need to group the values of multiple fields
- get their possible...
|
by: dunleav1 |
last post by:
I have an application that uses the old join syntax instead of the
SQL92 standards join syntax.
I need to justify changing the code to the new standard.
Is there any performance issue related to...
|
by: Paulo da Silva |
last post by:
Hi!
I was told in this NG that string is obsolet. I should use
str methods.
So, how do I join a list of strings delimited by a given
char, let's say ','?
Old way:
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |