Database

SQL Left Outer Join Problem

7-Nov-14 7:33

Ibrahim.elh wrote:
what you proposed?

Create a small application, connect to the source-database, connect to the target-database, read a record from source, write a record to target.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Vimalsoft(Pty) Ltd4-Nov-14 21:53

Vimalsoft(Pty) Ltd

4-Nov-14 21:53

i have Join that i expect to give me 3 Records but it gives me to Records . There is a Record in Table A and in table "C" there are two records , i used a left outer Join and i only get two records instead of 3. i dont want to use a Union to do this. Below is an Example

SQL

CREATE TABLE #TABLEA 
(
LIS_KEY VARCHAR(MAX) NULL,
FUNC_KEY VARCHAR(MAX) NULL  
)

INSERT INTO #TABLEA 
VALUES('047600055/R',NULL)


CREATE TABLE #TABLEC 
(
LIS_KEY VARCHAR(MAX) NULL,
FUNC_KEY VARCHAR(MAX) NULL  
)


INSERT INTO #TABLEC 
VALUES('047600055/R','GEOSS001')


INSERT INTO #TABLEC 
VALUES('047600055/R','GEOSS002')

SELECT * FROM   #TABLEA A 
left outer  JOIN   #TABLEC C 
ON  A.LIS_KEY  =  C.LIS_KEY     
WHERE A.LIS_KEY  = '047600055/R'

Thanks

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com

Vimalsoft(Pty) Ltd4-Nov-14 22:11

Vimalsoft(Pty) Ltd

4-Nov-14 22:11

Got the Solution

SQL

SELECT *
FROM
(
SELECT * FROM   #TABLEA A 
UNION ALL
SELECT * FROM   #TABLEC C 
)t   
WHERE LIS_KEY  = '047600055/R'

Mycroft Holmes4-Nov-14 22:33

Mycroft Holmes

4-Nov-14 22:33

What happened to I don't want to use UNION Laugh | :laugh:

Never underestimate the power of human stupidity
RAH

Vimalsoft(Pty) Ltd4-Nov-14 22:34

Vimalsoft(Pty) Ltd

4-Nov-14 22:34

i take back my words Laugh | :laugh:

Re: New SQLite Management Program

Richard Deeming5-Nov-14 1:42

Richard Deeming

5-Nov-14 1:42

Two rows would be the expected result for a LEFT JOIN with that data.

Each row from the table on the left will be returned once for each matching row in the table on the right, or once if there are no matching rows. Since the table on the right only has two rows, and the table on the left only has one row, the result will be two rows.

Jeff Atwood has a good visual explanation of SQL joins:
http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/[^]

Another way to explain joins:

Product the Cartesian Product of the two tables - each row in the left-hand table is matched with each row in the right-hand table.
Remove the rows where the JOIN condition is not met.
Depending on the JOIN type:
- INNER JOIN: Nothing to do.
- LEFT (OUTER) JOIN: Any rows in the left-hand table but not in the results are added back, matched with a row of NULL values for the right-hand table.
- RIGHT (OUTER) JOIN:: Any rows in the right-hand table but not in the results are added back, matched with a row of NULL values for the left-hand table.
- FULL (OUTER) JOIN: Apply the rules for both LEFT and RIGHT joins.
Filter the result based on the conditions in the WHERE clause (if any).

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Message Closed

3-Nov-14 12:07

Member 2419427

3-Nov-14 12:07

Message Closed

PIEBALDconsult3-Nov-14 12:48

PIEBALDconsult

3-Nov-14 12:48

Too spammy. Please consider the Product Showcase.

SSRS across Internet

JM7631-Oct-14 6:52

31-Oct-14 6:52

Hi everybody
I was looking information about this tip, a found a lot of information, but still no clear to me.
I would like to know what setup I have to make to view my reports across Internet.
I have read that is possible, from this link, but my topology is more simple http://technet.microsoft.com/es-es/library/ms159272%28v=sql.105%29.aspx[^]

But still I have somo doubt about the settings.
The topology is PC with SQL Server and SSRS ---> ROUTER ----> PC Client

What I did until now
Setup SSRS for network Service
Setup firewall port http://PC:8080/Report
Setup router for the same port

Try to access from my IPPublic to http://188.234.xxx.xxx:8080/Report

But didn´t work...

Any suggestion, or tip, will be gratefull Wink | ;)

GuyThiebaut3-Nov-14 1:43

GuyThiebaut

3-Nov-14 1:43

First question - have you got SSRS up and running for those on the local network, can users see reports when on the local network?

Second question - what does 'didn't work' mean, what error messages did you get?

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

modified 3-Nov-14 10:07am.

JM763-Nov-14 5:52

3-Nov-14 5:52

Hi, thank you very much for your interest

First question - have you got SSRS up and running for those on the local network, can users see reports when on the local network?

Yes, inside the network everything is working fine

Second question - what does 'didn't work' mean, what error messages did you get?

The problem is the browser, Internet explorer can't find the web page.
I don´t know what is wrong with my configuration.

Thank you

GuyThiebaut3-Nov-14 6:12

GuyThiebaut

3-Nov-14 6:12

JM76 wrote:
Internet explorer can't find the web page

That's still not telling me the error message you receive.
Are you getting a 404, what is the exact wording of the error message?

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Eddy Vluggen3-Nov-14 8:14

3-Nov-14 8:14

JM76 wrote:
The problem is the browser, Internet explorer can't find the web page.

If your browser cannot open Google, is that the browsers fault? I'd first check connectivity, in particular NAT.

Download a portable Apache-server and put it on USB. Start that on the machine that you are trying to reach. That way you can verify whether the computer can be found and the TCP settings/firwall/router is correct. I'm going to guess that the translation of the external IP to an internal IP is incorrect.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

JM765-Nov-14 0:25

5-Nov-14 0:25

GuyThiebaut
That's still not telling me the error message you receive.
Are you getting a 404, what is the exact wording of the error message?

Eddy Vluggen
If your browser cannot open Google, is that the browsers fault? I'd first check connectivity, in particular NAT.

Download a portable Apache-server and put it on USB. Start that on the machine that you are trying to reach. That way you can verify whether the computer can be found and the TCP settings/firwall/router is correct. I'm going to guess that the translation of the external IP to an internal IP is incorrect.

Hi Mr.GuyThiebaut and Mr.Eddy Vluggen
Thank you very much for your reply and sorry for my delay. Right now I´m out until the next monday. I can´t prove anything more. As soon as I come back I´m going to prove it and then I´ll reply

JM7610-Nov-14 6:02

10-Nov-14 6:02

That's still not telling me the error message you receive.
Are you getting a 404, what is the exact wording of the error message?

If your browser cannot open Google, is that the browsers fault? I'd first check connectivity, in particular NAT.

Download a portable Apache-server and put it on USB. Start that on the machine that you are trying to reach. That way you can verify whether the computer can be found and the TCP settings/firwall/router is correct. I'm going to guess that the translation of the external IP to an internal IP is incorrect.

Hi Christopher & Eddy

Please take a look to the following image, if you can, please try yourself.
From the client I do not get any error message

If you try to do a telnet, you can see this work perfectly, so this means that port forwarding is right.

http://i57.tinypic.com/ms2glu.png[^]

Eddy Vluggen10-Nov-14 8:12

10-Nov-14 8:12

JM76 wrote:
Please take a look to the following image

If that is a screenshot from your local network, using the outside adress, then yes, I'd expect it to not find it.

From any other computer outside the network, it should be visible. It ain't from my place - something accepts the connection, but that's about it.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

JM7610-Nov-14 8:23

10-Nov-14 8:23

Hi Eddy, thank you very much!!!

No, the screenshot is from outside of my network, I´m connected with another public IP.
When I try connect from my network, inside the Lan, everything is fine.
I don´t know what else I can try.

Eddy Vluggen11-Nov-14 0:31

11-Nov-14 0:31

I would have expected the other way around Smile | :)

I can't TeamView, but if I did, the first thing I'd be doing is downloading a webserver. As soon as the external IP in a browser shows the "it works" page from Apache, I'd replace the server with another app (SSRS in your case).

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

JM7611-Nov-14 6:58

11-Nov-14 6:58

I have tried the following, Install IIS and try to access from outside, and works perfectly.

Download a portable Apache-server and put it on USB. Start that on the machine that you are trying to reach. That way you can verify whether the computer can be found and the TCP settings/firwall/router is correct. I'm going to guess that the translation of the external IP to an internal IP is incorrect.

Sorry Eddy, but I don´t understand what have to do.
I have download xampp portable http://portableapps.com/apps/development/xampp[], but I´m a litle lost, If you can guide me ...

Eddy Vluggen11-Nov-14 7:52

11-Nov-14 7:52

JM76 wrote:
I don´t understand what have to do.

You already done it; whether it's tested with IIS or Apache makes little difference. If you can connect from the outside and see the IIS-page, then the network-settings are correct.

To summarize; you have an application that servers webpages (IIS) and that is reachable from outside of the local network. You have another application (SSRS) that works correctly on the network, but does not talk to the outside world.

Does SSRS require credentials? I had expected an error-message if it is missing a authentication, but I can imagine that there's some kind of "lock" on SSRS to prevent anonymous browsing of the database.

Apparently it is complicated enough to write a book[^] on the subject.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

JM7612-Nov-14 20:56

12-Nov-14 20:56

Hi Eddy
Finally SOLVED!!! After reading your linkhttp://searchitchannel.techtarget.com/feature/SQL-Server-2008-Reporting-Services-for-Internet-deployment[^]
All I have done, is edit the file rsreportserver.config and change the tag Authentication

HTML

<Authentication>
    <AuthenticationTypes>
        <RSWindowsBasic/>
        <RSWindowsKerberos/>
    </AuthenticationTypes>
    <RSWindowsExtendedProtectionLevel>Off</RSWindowsExtendedProtectionLevel>
    <RSWindowsExtendedProtectionScenario>Proxy</RSWindowsExtendedProtectionScenario>
    <EnableAuthPersistence>true</EnableAuthPersistence>
</Authentication>

After this works perfectly.

Thank you very much

Eddy Vluggen13-Nov-14 5:41