November 11, 2008 at 6:54 pm
Hi Guys,
I am trying to advance my very basic sql statement knowledge.
Looking at Select statements, there are two things I want to try and achieve.
Firstly, is cross table referencing available. For example, I have one table lets call it customers, and another call invoices. The customers table has a key field of cus_id, each line in the invoice table has a corresponding cus_id column also, although obviously not a unique key.
How can I select rows from the invoice table based on other criteria in the customers table, lets say for example the customer table has a colum called 'type' with entries of "A" or "B". Can I have a select statement that says get me all the rows in the invoice table where the CUS_ID column corresponds to a CUS_ID in the custmers tabel that is type B ??
Secondly, can I create wildcards when using the = function. Reason for asking. If I have a SQL Statement with an optional variable, going back to our CUS_ID int he customer table, I might have the SQL statement with the CUS_ID as an inputted variable and therefore return the results pertaining to the section. eg select * from CUSTOMERS where CUS_ID = "'" VARIABLE "'"
The problem with the above is that I want the variable to allow a blank entry and thus return all results, preferably without altering the structure of the statement. If I leave it blank at the moment it just doesn't return anything (as expected), though I can only use wildcards like % if I change the = to 'like' Though this can be acheived, it would be better if I could have = 'wildcard'.
Any suggestions gratefully recevied on either query,
matt
November 12, 2008 at 11:06 am
bump
November 12, 2008 at 11:18 am
m.dunster (11/12/2008)
bump
No need to bump every couple of hours. This is a forum, not a chatroom.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2008 at 11:26 am
m.dunster (11/11/2008)
Hi Guys,I am trying to advance my very basic sql statement knowledge.
Looking at Select statements, there are two things I want to try and achieve.
Firstly, is cross table referencing available. For example, I have one table lets call it customers, and another call invoices. The customers table has a key field of cus_id, each line in the invoice table has a corresponding cus_id column also, although obviously not a unique key.
Sure. Look up the join keyword in Books Online
Can I have a select statement that says get me all the rows in the invoice table where the CUS_ID column corresponds to a CUS_ID in the custmers tabel that is type B ??
Absolutely. Join the tables together and then apply the conditions you want
Secondly, can I create wildcards when using the = function.
No. Wild cards require LIKE
If I may suggest... There's a new book recently published called T-SQL Fundamentals by Itzik Ben-Gan. I think it would help you a lot and Itzik is an excellent author. I haven't received my copy yet, but I expect that it will be a very good book.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2008 at 11:26 am
thanks, that helps out
November 12, 2008 at 11:32 am
To answer your first question, you would JOIN the tables in your select query. By joining the tables, you now have access to all columns from both tables. So, you could do the following:
SELECT cus.CustomerName
,inv.InvoiceNumber
FROM dbo.Customers cus
INNER JOIN dbo.Invoices inv ON inv.cus_id = cus.cus_id
WHERE cus.Type = 'A';
As for your second question, I would just use LIKE with a variable and then make sure the variable includes wildcards when I need them. For example:
DECLARE @MyVariable varchar(20);
SET @MyVariable = '%' -- return all rows
SELECT ...
FROM dbo.Customers
WHERE CustomerName Like @MyVariable;
SET @MyVariable = 'JOHN%'; -- return all rows that begin with JOHN
SELECT ...
FROM dbo.Customers
WHERE CustomerName Like @MyVariable;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply